(* ::Package:: *)

(* ::Subsection:: *)
(*Initialization*)


(* ::Input::Initialization:: *)
Needs/@{"DatabaseLink`","KnotTheory`"};


(* ::Input::Initialization:: *)
(*Edit the path accordingly before executing*)
sqlPath="/Users/shana/data-V/";

(*Names of databases and tables you want to access
Files should all be stored under the directory sqlPah points to*)
schema=<|"V-database_3-16c.db"->Table["V"<>ToString[i],{i,1,4}],
"V-database_17c-loose.db"->{"V1","V2"},
"V-database_18c-loose.db"->{"V1","V2"},
"V1-database_17a.db"->{"V1"},
"V1-database_18a.db"->{"V1"}|>;

(*The rest is automatic*)

Assert[Length[schema]>0];

tableDict=<||>;
aliasDict=<||>;


conn=OpenSQLConnection[JDBC["SQLite",":memory:"]];

dbNames=Keys[schema];
Do[sqlName=dbNames[[i]];
alias="db"<>ToString[i];
aliasDict[sqlName]=alias;
tables=schema[sqlName];
Scan[(tableName=#;
If[KeyExistsQ[tableDict,tableName],AppendTo[tableDict[tableName],alias],tableDict[tableName]={alias}])&,tables];
SQLExecute[conn,"ATTACH DATABASE ? AS ?",{sqlPath<>sqlName,alias}];,{i,1,Length[dbNames]}];


Scan[(tableName=#;
aliases=tableDict[tableName];selectStatements=Map["SELECT * FROM "<>#<>"."<>tableName&,aliases];
unionQuery=StringRiffle[selectStatements," UNION ALL "];
viewName="all_"<>tableName;createViewSql="CREATE TEMPORARY VIEW "<>viewName<>" AS "<>unionQuery;
SQLExecute[conn,createViewSql];)&,Keys[tableDict]];


(* ::Subsection:: *)
(*Main Methods*)


(* ::Subsubsection:: *)
(*Vn Polynomials*)


(* ::Input::Initialization:: *)
KnotInfo[n_,K_]:=AssociationThread[
{"SnapPy Name", "PD Code", "Genus", "Degree of Alexander Polynomial", "Tightness", "V"<>ToString[n]<>" Polynomial"},SQLSelect[conn,"all_V"<>ToString[n],{"\"SnapPy Name\"", "\"PD Code\"", "Genus","\"Degree of Alexander Polynomial\"", "Tightness", "\"V"<>ToString[n]<>" Polynomial\""},SQLColumn["\"KnotTheory Name\""]==ToString[K]][[1]]];

SnapPyName[n_,K_]:=KnotInfo[n,K]["SnapPy Name"];

KnotTable[n_Integer,c_Integer]:=If[c<=16,Sort[ToExpression/@Flatten[
SQLExecute[conn,
"
        SELECT \"KnotTheory Name\"
        FROM \"all_V" <>ToString[n]<>"\"
        WHERE CAST(
            SUBSTR(\"KnotTheory Name\", 6, INSTR(\"KnotTheory Name\", ',') - 6)
            AS INTEGER) = "<>ToString[c]
]
]],
Sort[Flatten[
SQLExecute[conn,
"
        SELECT \"SnapPy Name\"
        FROM \"all_V" <>ToString[n]<>"\"
        WHERE CAST(
            SUBSTR(\"SnapPy Name\", 1, LENGTH(\"SnapPy Name\") - LENGTH(LTRIM(\"SnapPy Name\", '0123456789')))
            AS INTEGER) = "<>ToString[c]
]
]]
];

Genus[n_,K_]:=ToExpression[SQLSelect[conn,"all_V"<>ToString[n],{"Genus"},SQLColumn["\"KnotTheory Name\""]==ToString[K]][[1,1]]];

AlexDeg[n_,K_]:=ToExpression[SQLSelect[conn,"all_V"<>ToString[n],{"\"Degree of Alexander Polynomial\""},SQLColumn["\"KnotTheory Name\""]==ToString[K]][[1,1]]];

TightQ[n_,K_]:=ToExpression[SQLSelect[conn,"all_V"<>ToString[n],{"Tightness"},SQLColumn["\"KnotTheory Name\""]==ToString[K]][[1,1]]]===1;

V[n_Integer,K_]:=Module[{vStringL =SQLSelect[conn,"all_V"<>ToString[n],{"\"V"<>ToString[n]<>" Polynomial\""},SQLColumn["\"KnotTheory Name\""]==ToString[K]],vString},
If[Length[vStringL] ==0,
$Failed,
vString = vStringL[[1,1]];
If[vString == "" || StringContainsQ[vString, "k"|"RF"|"X"],
$Failed,
ToExpression[vString]
]]
];

PDFromSQL[n_,K_]:=PD@@(X@@#&/@ToExpression[StringReplace[SQLSelect[conn,"all_V"<>ToString[n],{"\"PD Code\""},SQLColumn["\"KnotTheory Name\""]==ToString[K]][[1,1]],
{"["-> "{", "("-> "{", "]"->"}",")"->"}"}]]);
